package net.core.service.jxl;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.core.base.ibatis.BaseSqlMapDAO;

public class OutExcelImpl extends BaseSqlMapDAO implements OutExcelService {
	
public static final int EXCEL_LAB_SUM = 484836;
	
	private final String getColName(String[] colName){
		String colStr = "";
		for(int i=0; i<colName.length; i++){
			colStr+=colName[i] + ";;";
		}
		
		return colStr.substring(0, colStr.length()-2);
	}
	
	public boolean expExcel(String[][] alt, String title, String ti, String[] colName, String fileName, String sysDate, String personName, int shtRow){
		
		return this.expExcel(alt, title,  this.getColName(colName), ti,fileName, sysDate, personName, shtRow);
	}
	
	public boolean expExcel(List<Map<String, String>> lt, String title, String ti,  String[] colName, String fileName, String sysDate, String personName, int shtRow){
		
		return this.expExcel(lt, title, this.getColName(colName), ti,fileName, sysDate, personName, shtRow);
		
	}
	public boolean expExcel(String[][] alt, String title, String ti, String colName, String fileName, String sysDate, String personName, int shtRow){
		
		List<Map<String, String>> lt = new ArrayList<Map<String, String>>();
		for(int i=0; i<alt.length; i++){
			String [] _alt = alt[i];	//取出单条记录
			Map<String, String> map = new HashMap<String, String>();
			//将记录打包到map中
			for(int j=0; j<_alt.length; j++){
				map.put(String.valueOf(j+1), _alt[j]);
			}
			//添加到lt中
			lt.add(map);
		}
		return lt.isEmpty() ? false : this.expExcel(lt, title, ti, colName, fileName, sysDate, personName, shtRow);
	}
	
	private boolean findStr(String str, String splitStr){
		if(str.indexOf(splitStr) > 0){
			return true;
		}
		return false;
	}
	
	public boolean expExcel(List<Map<String, String>> lt, String title, String ti, String colName, String fileName, String sysDate, String personName, int shtRow) 
	{
			int rowNo = lt.size(); //总记录数 数据库取出
			int pageNo = 1;	//记录当时页数
			boolean splitStr = false;
			String[] tis = ti.split(";;");
			if(this.findStr(colName, "MERGECELLS-SUNNADA")){
				colName = colName.replace("MERGECELLS-SUNNADA", "");
				splitStr = true;
			}
			String [] ts = colName.split(";;");
	
			//选判断合计单元格是否超过484836条记录。否则过大无法导出
			if(rowNo * ts.length > EXCEL_LAB_SUM){
				System.out.println("超过最大记录");
				return false;
			}
			//判断有多少条记录，当记录数超过指定数时分页
			if(rowNo > shtRow){
				pageNo = rowNo%shtRow != 0 ? rowNo/shtRow + 1 : rowNo/shtRow; 
			}
			try{
				WritableWorkbook book = Workbook.createWorkbook(new File(fileName));
				WritableSheet sheet;
				//设置字体跟列居中
				//大标题
				WritableFont fontT = new  WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD);
				WritableCellFormat formatT = new  WritableCellFormat(fontT);
				formatT.setAlignment(jxl.format.Alignment.CENTRE);
				formatT.setBorder(Border.ALL, BorderLineStyle.THIN,
					     jxl.format.Colour.BLACK);
				//小标题
				WritableFont fontTitleSty = new  WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD);
				WritableCellFormat formatTitle = new  WritableCellFormat(fontTitleSty);
				formatTitle.setAlignment(jxl.format.Alignment.CENTRE);
				formatTitle.setBackground(jxl.format.Colour.GREY_25_PERCENT);
				formatTitle.setBorder(Border.ALL, BorderLineStyle.THIN,
					     jxl.format.Colour.BLACK);
				//表格数据
				WritableFont fontSty = new  WritableFont(WritableFont.ARIAL, 10);
				WritableCellFormat formatSty = new  WritableCellFormat(fontSty);
				formatSty.setAlignment(jxl.format.Alignment.CENTRE);
				formatSty.setWrap(true);
				formatSty.setBorder(Border.ALL, BorderLineStyle.THIN,
					     jxl.format.Colour.BLACK);
				
				//根据页数创建工作区
				for(int p=0; p<pageNo; p++){
					sheet = book.createSheet("第"+(p+1)+"页", p);
					jxl.write.Label label;
					//合并单元格
					sheet.mergeCells(0, 0, tis.length, 0);	//加上了序列
					label = new jxl.write.Label(0, 0, title, formatT);
					sheet.addCell(label);
					
					//添加标题，操作时间，人员
					label = new jxl.write.Label(0, 1, "操作日期",formatSty);
					sheet.addCell(label);
					label = new jxl.write.Label(1, 1, sysDate, formatSty);
					sheet.addCell(label);
					label = new jxl.write.Label(2, 1, "操作人员", formatSty);
					sheet.addCell(label);
					label = new jxl.write.Label(3, 1, personName, formatSty);
					sheet.addCell(label);
					int rolNum = 3;
					//设置标题
					for (int i=0, ln = 0, lv=0; i<ts.length; i++)
					{
						/**
						 * 第一个参数为列,第二个参数为行,第三个参数为值
						 */
						if(splitStr){
							//如里返回值为真说明有找到
							//设置标题
							if(this.findStr(ts[i],"##")){	//查找是否有分列
								String tsN = ts[i].toString();
								String _tmpTs [] = tsN.split("##");
								String tmpTsN = _tmpTs[1].toString();
								String _tmpTsDetail [] = tmpTsN.split("@");
								
								sheet.mergeCells(i+ lv, rolNum, i+lv+_tmpTsDetail.length -1, rolNum);
								label = new jxl.write.Label(i+lv, rolNum, _tmpTs[0], formatTitle);
								sheet.addCell(label);
								sheet.setColumnView(i+lv, (_tmpTs[0].length()*5));
								for(int j = 0; j< _tmpTsDetail.length; j++){
									String _tmpTsDetailStr = _tmpTsDetail[j].toString();
									label = new jxl.write.Label((i-lv)+ln, rolNum+1, _tmpTsDetailStr, formatTitle);
									sheet.addCell(label);
									sheet.setColumnView((i-lv)+ln, (_tmpTs[0].length()*5)/_tmpTsDetail.length);
									ln++;
								}
								lv = lv + _tmpTsDetail.length - 1;
							}else{
								sheet.mergeCells((i-lv)+ln, rolNum, (i-lv)+ln, rolNum+1);
								label = new jxl.write.Label((i-lv)+ln, rolNum, ts[i], formatTitle);
								sheet.addCell(label);
								sheet.setColumnView((i-lv)+ln, (ts[i].length()*5));
							}
						}else{
							label = new jxl.write.Label(i, rolNum, ts[i],formatTitle);
							sheet.addCell(label); 
							sheet.setColumnView(i, (ts[i].length()*5));
						}
					}
					if(splitStr) rolNum = rolNum + 1;	//设置标题列
					
					//设置序号列宽度
					
					//写入记录数
					for (int j = 1; j < (p != (pageNo-1) ? shtRow+1 : rowNo%shtRow == 0 ? shtRow+1 : (rowNo%shtRow)+1); j++)
					{
						//先添加序号
						jxl.write.Number number = new jxl.write.Number(0, j+rolNum, j+(p*shtRow), formatSty);
						//System.out.println(number);
						
						sheet.addCell(number);
						HashMap<String, String> map = (HashMap<String, String>)lt.get(j-1);
						for(int m=1; m < tis.length + 1;  m++){
							String val = map.get(tis[m-1].trim()) == null ? " " :  String.valueOf(map.get(tis[m-1].trim()));
							label = new jxl.write.Label(m, j+rolNum, val, formatSty); //设置第一列为标题
							sheet.addCell(label);
							//System.out.println("value: "+val + "length: "+val.length());
							//sheet.setColumnView(m, (val.length() * 2));
						}
					}
				}
				book.write();
				book.close();
			}catch (Exception e) {
				e.printStackTrace();
				return false;
			}
			System.out.println("write end");
			return true;
			
	}
	public String formatNum(float f){
		java.text.DecimalFormat   df   =new   java.text.DecimalFormat("#0.00");
		return df.format(f);
	}
	
	// 多表头导出
	public boolean expExcel(List<Map<String, String>> lt, String title, String ti, List colName, String fileName,
			String sysDate, String personName, int shtRow) {
		int rowNo = lt.size(); // 总记录数 数据库取出
		int pageNo = 1; // 记录当时页数
		String[] tis = ti.split(";;");

		// 选判断合计单元格是否超过484836条记录。否则过大无法导出
		if (rowNo * colName.size() > EXCEL_LAB_SUM) {
			System.out.println("超过最大记录");
			return false;
		}
		// 判断有多少条记录，当记录数超过指定数时分页
		if (rowNo > shtRow) {
			pageNo = rowNo % shtRow != 0 ? rowNo / shtRow + 1 : rowNo / shtRow;
		}
		try {
			WritableWorkbook book = Workbook.createWorkbook(new File(fileName));
			WritableSheet sheet;
			// 设置字体跟列居中
			// 大标题
			WritableFont fontT = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
			WritableCellFormat formatT = new WritableCellFormat(fontT);
			formatT.setAlignment(jxl.format.Alignment.CENTRE);
			formatT.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);
			// 小标题
			WritableFont fontTitleSty = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
			WritableCellFormat formatTitle = new WritableCellFormat(fontTitleSty);
			formatTitle.setAlignment(jxl.format.Alignment.CENTRE);
			formatTitle.setBackground(jxl.format.Colour.GREY_25_PERCENT);
			formatTitle.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);
			// 表格数据
			WritableFont fontSty = new WritableFont(WritableFont.ARIAL, 10);
			WritableCellFormat formatSty = new WritableCellFormat(fontSty);
			formatSty.setAlignment(jxl.format.Alignment.CENTRE);
			formatSty.setWrap(true);
			formatSty.setBorder(Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.BLACK);

			// 根据页数创建工作区
			for (int p = 0; p < pageNo; p++) {
				sheet = book.createSheet("第" + (p + 1) + "页", p);
				jxl.write.Label label;
				// 合并单元格
				sheet.mergeCells(0, 0, tis.length, 0); // 加上了序列
				label = new jxl.write.Label(0, 0, title, formatT);
				sheet.addCell(label);

				// 添加标题，操作时间，人员
				label = new jxl.write.Label(0, 1, "操作日期", formatSty);
				sheet.addCell(label);
				label = new jxl.write.Label(1, 1, sysDate, formatSty);
				sheet.addCell(label);
				label = new jxl.write.Label(2, 1, "操作人员", formatSty);
				sheet.addCell(label);
				label = new jxl.write.Label(3, 1, personName, formatSty);
				sheet.addCell(label);
				int rolNum = 3;
				int maxRow = 1;
				// 设置标题
				if (colName.size() > 0) {
					ExcelHeader xls0 = (ExcelHeader) colName.get(0);
					maxRow = xls0.getMaxLevel();

					sheet.mergeCells(0, rolNum, 0, maxRow + rolNum - 1);
					label = new jxl.write.Label(0, rolNum, "序号", formatTitle);
					sheet.addCell(label);
					sheet.setColumnView(0, 10);
					// 
					for (int i = 0, curCol = 1, nextCol = 0; i < colName.size(); i++) {

						ExcelHeader xls = (ExcelHeader) colName.get(i);
						if (xls.getHasSon()) {
							sheet.mergeCells(curCol, rolNum + xls.getLevel(), xls.getColspan() + curCol - 1, xls
									.getRowspan()
									+ rolNum - 1);
							label = new jxl.write.Label(curCol, rolNum + xls.getLevel(), xls.getColname(), formatTitle);
							sheet.addCell(label);
							sheet.setColumnView(nextCol, (xls.getColname().length() * 5));
						} else {
							// mergeCells(a,b,c,d) 单元格合并函数
							// a:单元格的列号;b:单元格的行号;c:从单元格[a,b]起,向下合并的列数;d:从单元格[a,b]起,向下合并的行数
							// sheet.mergeCells(curCol, rolNum + xls.getLevel(),
							// xls.getColspan(), xls.getRowspan()+rolNum-1);
							label = new jxl.write.Label(curCol, rolNum + xls.getLevel(), xls.getColname(), formatTitle);
							sheet.addCell(label);
							sheet.setColumnView(curCol, (xls.getColname().length() * 5));
							curCol = curCol + xls.getColspan();
						}
						if (xls.level == 0) {
							nextCol = nextCol + xls.getColspan();
						}
					}
				}
				// 设置记录开始行
				rolNum = rolNum + maxRow - 1;

				// 写入记录数
				for (int j = 1; j < (p != (pageNo - 1) ? shtRow + 1 : rowNo % shtRow == 0 ? shtRow + 1
						: (rowNo % shtRow) + 1); j++) {
					// 先添加序号
					jxl.write.Number number = new jxl.write.Number(0, j + rolNum, j + (p * shtRow), formatSty);
					// System.out.println(number);

					sheet.addCell(number);
					HashMap<String, String> map = (HashMap<String, String>) lt.get(j - 1);
					for (int m = 1; m < tis.length + 1; m++) {
						String val = map.get(tis[m - 1].trim()) == null ? " " : String.valueOf(map.get(tis[m - 1]
								.trim()));
						label = new jxl.write.Label(m, j + rolNum, val, formatSty); // 设置第一列为标题
						sheet.addCell(label);
					}
				}
			}
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		System.out.println("write end");
		return true;
	}
}
